package service.impl;

import entity.CourseDO;
import req.CourseRequest;
import req.StudentGradeRequest;
import res.CourseDTO;
import res.StudentDTO;
import service.CourseService;
import service.StudentGradeService;
import util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

public class StudentGradeServiceImpI implements StudentGradeService {

    @Override
    public StudentDTO retrieveStudent(StudentGradeRequest request) {
        StringBuilder sql = new StringBuilder();
        sql.append("select * from grade ");
        if (request.getSearchKey() != null && !"".equals(request.getSearchKey().trim())) {
            sql.append(" where sno = " + request.getSearchKey().trim());
        }
        //sql.append("order by sno desc limit ").append(request.getStart()).append(",").append(request.getPageSize());
        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;
        StudentDTO returnDTO = new StudentDTO();
        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            //查寻记录
            returnDTO.setData(fillData(rs));

            sql.setLength(0);
            sql.append("select count(*) from grade ");
            if (request.getSearchKey() != null && !"".equals(request.getSearchKey().trim())) {
                sql.append(" where sno = " + request.getSearchKey().trim());
            }
            //System.out.println(sql);
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                int count = rs.getInt(1);
                returnDTO.setTotalCount(count);
            }
            return returnDTO;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return null;
    }
    private Vector<Vector<Object>> fillData(ResultSet rs) throws SQLException {
        Vector<Vector<Object>> data = new Vector<>();
        while (rs.next()) {
            //查寻到的记录
            Vector<Object> oneRecord = new Vector<>();
            int sno = rs.getInt("sno");
            int sname = rs.getInt("cno");
            String ssex = rs.getString("grades");
            String sage = rs.getString("level");
            String semester = rs.getString("semester");
//            String sdept = rs.getString("sdept");
//            String sspe = rs.getString("sspe");
//            String sclass = rs.getString("sclass");
            oneRecord.addElement(sno);
            oneRecord.addElement(sname);
            oneRecord.addElement(ssex);
            oneRecord.addElement(sage);
            oneRecord.addElement(semester);

            data.addElement(oneRecord);
        }
        return data;
    }
}
